package main.orga;

import java.awt.print.Printable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import main.staff.CreateTypesSql;
import main.util.SqlConstants;
import main.util.StringUtil;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.shiro.crypto.RandomNumberGenerator;
import org.apache.shiro.crypto.SecureRandomNumberGenerator;
import org.apache.shiro.crypto.hash.SimpleHash;
import org.apache.shiro.util.ByteSource;


public class InitStaffInfoFromExcel {
	//登录密码相关信息
	public static final RandomNumberGenerator randomNumberGenerator = new SecureRandomNumberGenerator();
	public static final String algorithmName = "SHA-256";
	public static final int hashIterations = 2;
	static String pswd="Zyzx@2016";//登录密码（明文）
	
	//组织机构信息参数
	static String orgaPath = "F:/组织.xlsx";
	static int oldOrgaIdLine = 0;
	static int newOrgaIdLine = 5;
	
	//员工信息参数
	static String inputPath = "F:/人员.xlsx";
	static String outputPath = "F:/人员已处理.xlsx";
	//static int oldStaffIdLine =3 ;
	static int staffNameLine =5;
	static int staffOldOrgaIdLine =3;
	static int staffJionDtLine =11 ;
	static int staffPhoneLine =6 ;
	static int newstaffidLine =1 ;
	static int staffNewOrgaIdLine =12 ;//导出时候用
	
	
	//需要操作的文件信息
	public static Sheet sheetForStaffInfo;
	public static Sheet sheetForHandled;
	public static FileWriter staffInfoSql;
	public static FileWriter passwordSql;
	public static FileWriter staffServiceTypeSql;
	public static FileWriter staffOrmKfSql;
	
	public static void main (String[] args) {
		InputStream inp;
		FileOutputStream oup;
		
		try {
			staffInfoSql =new FileWriter("E:/t_ucp_staffbasicinfo.sql");
			
			
			
			
			inp = new FileInputStream(inputPath);
			oup = new FileOutputStream(new File(outputPath));
			Workbook wb1 = WorkbookFactory.create(inp);
			
			sheetForStaffInfo = wb1.getSheetAt(0);
			
			//生成t_ucp_staffbasicinfo的插入语句，并插入新生成的staffid
			initStaffInfoTable();
			wb1.write(oup);
			
			
			InputStream inp2 = new FileInputStream(outputPath);
			Workbook wb2 = WorkbookFactory.create(inp2);
			sheetForHandled = wb2.getSheetAt(0);
			//生成t_ucp_password的插入语句
			passwordSql =new FileWriter("E:/t_ucp_password.sql");
			initPasswordTable();
			
			//生成t_ucp_staffservicetype的插入语句
			String serviceType ="gxytck";
			staffServiceTypeSql =new FileWriter("E:/t_ucp_staffservicetype("+serviceType+").sql");
			initStaffServiceTypeTable(serviceType);
			
			//生成t_ucp_staffservicetype的插入语句
			/*String serviceType2 ="otck";
			staffServiceTypeSql =new FileWriter("E:/t_ucp_staffservicetype("+serviceType2+").sql");
			initStaffServiceTypeTable(serviceType2);*/
			
			
			//生成t_ucp_staff_orm_kf的插入语句
			int oldIdLine=4;//需要进行映射的工号所在的列数
			String sysName="BOSS";//系统名称
			staffOrmKfSql =new FileWriter("E:/t_ucp_staff_orm_kf("+sysName+")126.sql");
			initStaffOrmKfTable(oldIdLine,sysName);
			
			//生成t_ucp_staff_orm_kf的插入语句
			int oldIdLine2=7;//需要进行映射的工号所在的列数
			String sysName2="CRM";//系统名称
			staffOrmKfSql =new FileWriter("E:/t_ucp_staff_orm_kf("+sysName2+")126.sql");
			initStaffOrmKfTable(oldIdLine2,sysName2);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	private static void initStaffInfoTable() throws EncryptedDocumentException, InvalidFormatException, IOException {
		//读取组织机构id映射关系
		List<Map<String,Object>> orgaList = queryOrgaOrmInfo();
		
		//读取员工表
		for(int i=1;i<sheetForStaffInfo.getLastRowNum()+1;i++){
			Map<String, Object> staffInfo = queryStaffInfoByRow(i);
			//生成新的员工id
			String newStaffId = staffInfo.get("newStaffId").toString();//staffInfo.get("newstaffid");//getNewStaffId(staffInfo);
			
			if(!newStaffId.matches("[a-zA-Z]{3}?[0-9]{4}?")){
				continue;
			}
			
			
			//staffInfo.put("newStaffId", newStaffId);
			//查询新组织机构id
			String staffnewOrgaId = "";
			String staffOldOrgaId = (String)staffInfo.get("staffOldOrgaId");;
			for (Map<String,Object> orgaInfo : orgaList) {
				String staffOldOrgaIdTemp = (String)orgaInfo.get("oldOrgaId");
				//System.out.println(staffInfo+"####"+staffOldOrgaId+"####"+orgaInfo);
				if (staffOldOrgaId.equals(staffOldOrgaIdTemp)) {
					staffnewOrgaId = (String)orgaInfo.get("newOrgaId");
					break;
				}
			}
			
			if(StringUtil.isEmpty(staffnewOrgaId)){
				System.out.println(newStaffId+"失败，组织机构不存在");
				continue;
			}
			
			//生成sql语句
			String staffId = "'"+newStaffId+"',";
			String staffName = "'"+(String)staffInfo.get("staffName")+"',";
			String staffState = "'1',";
			String staffIdStatus = "'01',";
			String dlevelid = "'002',";
			String secondPost = "'',";
			String dutyId = "'002',";
			String secondDuty = "'',";
			String orgaId = "'"+staffnewOrgaId+"',";
			String postId = "'002',";
			String joinDate = StringUtil.isEmpty(((String)staffInfo.get("staffJionDt")))?null+",":("'"+(String)staffInfo.get("staffJionDt")+"',");//注意当joindate为null和不为null时sql语句中的单引号是不同的
			String mobilePhone = StringUtil.isEmpty((String)staffInfo.get("staffPhone"))?"'',":("'"+(String)staffInfo.get("staffPhone")+"',");
			String email = "'',";
			String cityId = "'',";
			String provinceId = "'"+staffnewOrgaId.substring(0, 8)+"',";
			String staffType = "'',";
			String idCardNo = "'',";
			String channelId = "'',";
			String tenantId = "'001',";
			String authenRuleId = "'1',";
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String insertTime = "'"+df.format(new Date())+"'";
			String sql = "INSERT INTO `t_ucp_staffbasicinfo` "
					+"(`STAFFID`, `STAFFNAME`, `STAFFSTATE`, `STAFFIDSTATUS`, `DLEVELID`, " 
					+"`SECONDPOST`, `DUTYID`, `SECONDDUTY`, `ORGAID`, `POSTID`, "		
					+"`JOINDATE`, `MOBILEPHONE`, `EMAILADDRESS`, "		
					+"`CITYID`, `ProviceID`, `STAFF_TYPE`, `ID_CARD_NO`, `CHANNEL_ID`, "		
					+"`TENANT_ID`, `authen_rule_id`, `INSERTTIME`) "	
					+"VALUES "
					+"("+staffId+staffName+staffState+staffIdStatus+dlevelid
					+secondPost+dutyId+secondDuty+orgaId+postId
					+joinDate+mobilePhone+email
					+cityId+provinceId+staffType+idCardNo+channelId
					+tenantId+authenRuleId+insertTime+");\n";
			System.out.println(newStaffId+"、成功");
			staffInfoSql.write(sql);
			
			//更新newStaffId到excel
			int row = (int)staffInfo.get("row");
			sheetForStaffInfo.getRow(row).createCell(newstaffidLine);
			sheetForStaffInfo.getRow(row).getCell(newstaffidLine).setCellValue(newStaffId);
			sheetForStaffInfo.getRow(row).createCell(staffNewOrgaIdLine);
			sheetForStaffInfo.getRow(row).getCell(staffNewOrgaIdLine).setCellValue(staffnewOrgaId);
			
		}
		
		staffInfoSql.write("COMMIT;");
		staffInfoSql.close();
	}
	private static String getNewStaffId(Map<String, Object> staffInfo2) {
		String newStaffId = "";
		//只能根据一种规则生成staffid
		//递增id
		newStaffId = getIncreaseId(staffInfo2);
		//其他规则
		
		return newStaffId;
	}

	private static String getIncreaseId(Map<String, Object> staffInfo2) {
		String minId = "2070";//起始编号
		String prefix = "GXS";//前缀
		int id = Integer.parseInt(minId)+(int)staffInfo2.get("row")-1;
		String staffId = prefix+String.format("%0"+minId.length()+"d", id);
		return staffId;
	}

	private static Map<String, Object> queryStaffInfoByRow(int i) {
		Row row = sheetForStaffInfo.getRow(i);
		//String oldStaffId=CreateTypesSql.removeNull(row, oldStaffIdLine);
		String newstaffid=SqlConstants.getColValue(row, newstaffidLine);
		String staffName=SqlConstants.getColValue(row, staffNameLine);
		String staffOldOrgaId=SqlConstants.getColValue(row, staffOldOrgaIdLine);
		String staffJionDt=SqlConstants.getColValue(row, staffJionDtLine);
		String staffPhone=SqlConstants.getColValue(row, staffPhoneLine);
		Map<String, Object> staffMap = new HashMap<String, Object>();
		//staffMap.put("oldStaffId", oldStaffId);
		staffMap.put("newStaffId", newstaffid);
		staffMap.put("staffName", staffName);
		staffMap.put("staffOldOrgaId", staffOldOrgaId);
		staffMap.put("staffJionDt", staffJionDt);
		staffMap.put("staffPhone", staffPhone);
		staffMap.put("row", i);
		return staffMap;
	}

	private static List<Map<String, Object>> queryOrgaOrmInfo() throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		InputStream inpOrga = new FileInputStream(orgaPath);
		Workbook orgaWb = WorkbookFactory.create(inpOrga);
		Sheet orgaSheet = orgaWb.getSheetAt(0);
		List<Map<String,Object>> orgaList = new ArrayList<Map<String,Object>>();
		for(int i=1;i<orgaSheet.getLastRowNum()+1;i++){
			Map<String, Object> orgaMap = new HashMap<String, Object>();
			Row row = orgaSheet.getRow(i);
			String oldOrgaId=CreateTypesSql.removeNull(row, oldOrgaIdLine);
			String newOrgaId=CreateTypesSql.removeNull(row, newOrgaIdLine);
			orgaMap.put("oldOrgaId", oldOrgaId);
			orgaMap.put("newOrgaId", newOrgaId);
			orgaList.add(orgaMap);
		}	
		return orgaList;
	}

	private static void initPasswordTable() throws IOException {
		for(int i=1;i<sheetForHandled.getLastRowNum()+1;i++){
			Row row = sheetForStaffInfo.getRow(i);
			String newStaffid=CreateTypesSql.removeNull(row, newstaffidLine);
			
			if(!newStaffid.matches("[a-zA-Z]{3}?[0-9]{4}?")){
				continue;
			}
			
			String orgaId=CreateTypesSql.removeNull(row, staffNewOrgaIdLine);
			if(StringUtil.isEmpty(orgaId)){
				System.out.println(newStaffid+"失败，组织机构不存在");
				continue;
			}
			
			//拼接sql语句
			String staffid = "'"+newStaffid+"',";
			String salttemp= randomNumberGenerator.nextBytes().toHex();
			String passwordTemp= new SimpleHash(algorithmName, pswd, ByteSource.Util.bytes(newStaffid + salttemp),
					hashIterations).toHex();
			String password = "'"+passwordTemp+"',";
			String salt = "'"+salttemp+"',";
			String beginDt= null+",";
			String endDt= "'2020-12-08 15:35:58',";
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String updateTime = "'"+df.format(new Date())+"'";
			String sql = "INSERT INTO `t_ucp_password` "
					+"(`STAFFID`, `PASSWORD`, `salt`, `BEGINDATE`, `ENDDATE`, `UPDATEDATE`) "
					+"VALUES "
					+"("+staffid+password+salt+beginDt+endDt+updateTime+");\n";
			System.out.println(sql);
			passwordSql.write(sql);

		}
		passwordSql.write("COMMIT;");
		passwordSql.close();
	}
	private static void initStaffServiceTypeTable(String serviceType) throws IOException {
		for(int i=1;i<sheetForHandled.getLastRowNum()+1;i++){
			Row row = sheetForStaffInfo.getRow(i);
			String newStaffid=CreateTypesSql.removeNull(row, newstaffidLine);
			
			if(!newStaffid.matches("[a-zA-Z]{3}?[0-9]{4}?")){
				continue;
			}
			String orgaId=CreateTypesSql.removeNull(row, staffNewOrgaIdLine);
			if(StringUtil.isEmpty(orgaId)){
				System.out.println(newStaffid+"失败，组织机构不存在");
				continue;
			}
			
			//拼接sql语句
			String sql = "INSERT INTO `t_ucp_staffservicetype` (`ServiceTypeId`, `StaffId`) "
					+"VALUES "
					+"('"+serviceType+"','"+newStaffid+"');\n";
			System.out.println(sql);
			staffServiceTypeSql.write(sql);

		}
		staffServiceTypeSql.write("COMMIT;");
		staffServiceTypeSql.close();
		
	}
	private static void initStaffOrmKfTable(int oldIdLine,String sysName) throws IOException {
		for(int i=1;i<sheetForHandled.getLastRowNum()+1;i++){
			Row row = sheetForStaffInfo.getRow(i);
			String newStaffid=CreateTypesSql.removeNull(row, newstaffidLine);
			if(!newStaffid.matches("[a-zA-Z]{3}?[0-9]{4}?")){
				continue;
			}
			
			String oldOrmId=CreateTypesSql.removeNull(row, oldIdLine);
			if(StringUtil.isEmpty(oldOrmId)){
				continue;
			}
			
			String orgaId=CreateTypesSql.removeNull(row, staffNewOrgaIdLine);
			if(StringUtil.isEmpty(orgaId)){
				System.out.println(newStaffid+"失败，组织机构不存在");
				continue;
			}
			
			
			//拼接sql语句
			String staffId = "'"+newStaffid+"',";
			String provId = "'"+orgaId.substring(0, 8)+"',";
			String sysNo = "'"+sysName+"',";
			String origStaffId = "'"+oldOrmId+"',";
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String crtTime = "'"+df.format(new Date())+"',";
			String updateTime = null;
			String sql = "INSERT INTO `t_ucp_staff_orm_kf` "
					+ "(`STAFF_ID`, `PROV_ID`, `SYS_NO`, `ORIG_STAFF_ID`, `CRT_TIME`, `UPDATE_TIME`) "
					+"VALUES "
					+"("+staffId+provId+sysNo+origStaffId+crtTime+updateTime+");\n";
			System.out.println(sql);
			staffOrmKfSql.write(sql);

		}
		staffOrmKfSql.write("COMMIT;");
		staffOrmKfSql.close();
		
	}

	
}
